﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Runtime.Serialization.Json;
using System.Collections;
using System.Web;
using System.Data;

namespace lxwmvc
{
    public enum guanlian
    {
        inner_join,
        left_join,
        right_join,
        full_join,
        empty
    }
    public class ti
    {
        public guanlian guanlian { get; set; }
        public string on { get; set; }
    }
    public class supermodel : Hashtable
    {
           //表，主键，       不填写，就是*
            //supermodel.select("fuwurenid").form("fuwurenyuan").where("a=1 and b=c").more();rows
            //                                                                       .one();object 第一行 第一列
            //                                                                       .hash();hash表

            //supermodel.update("fuwurenyuan").set("k","v").set("k","v").where("xxxxx");
            //supermodel.delete("fuwurenyuan").where("xxxx");
            
            //supermodel.saveorupdate();
           
         
            //supermodel.show(pb)
            //supermodel.pager()
         //public supermodel 


        Dictionary<string, ti> tablelist = new Dictionary<string, ti>();
        Dictionary<string, object> setlist = new Dictionary<string, object>();

        string _where = "";
        string _orderby = "";
        string _pk = "";
        string _select = "*";

        /// <summary>
        /// 初始化
        /// </summary>
        /// <returns></returns>
        public static supermodel create(string tablename,pagebase pb = null,string qianzhui = "")
        {            
            supermodel m = null;
            if (pb != null) m = (supermodel)pb.getmodel(tablename,qianzhui);
            else m = new supermodel();
            //插入表
            tablename = tablename.ToLower();
            m.tablelist[tablename] = new ti() { guanlian= guanlian.empty, on="" };
            //得到真实的表
            if (!applicationhelper.pks.ContainsKey(m._tablename))
            {
                return null;
                throw new Exception(m._tablename + "此表没有递增主键!");
            }

            m._pk = applicationhelper.pks[m._tablename];          
            return m;
        }

        /// <summary>
        /// 返回主键
        /// </summary>
        public string PK
        {
            get { return _pk; }
        }

        /// <summary>
        /// update
        /// </summary>
        /// <param name="k"></param>
        /// <param name="v"></param>
        /// <returns></returns>
        public supermodel set(string k, object v)
        {
            setlist[k] = v;
            return this;
        }

        //public T this[string ]
        public object this[string key] 
        {
            get {
                if(base.ContainsKey(key))
                    return base[key];
                return "";
            }
            set {
                base[key] = value;
            }
        }

        /// <summary>
        /// 输出出去
        /// </summary>
        /// <param name="pb"></param>
        /// <param name="qianzui"></param>
        public void show(pagebase pb, string qianzui = "")
        {
            foreach (string s in this.Keys)
            {
                this[s.ToLower()] = this[s];
                if (qianzui!=null && qianzui.Length > 0)
                    pb[qianzui + "_" + s.ToLower()] = this[s];
                else
                    pb[s.ToLower()] = this[s];
            }
        }

        /// <summary>
        /// 输出page信息
        /// </summary>
        /// <param name="pb"></param>
        /// <returns></returns>
        public pageinfo pager(pagebase pb)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var o in tablelist)
            {
                sb.Append(" ");
                if (o.Value.guanlian != guanlian.empty)
                {
                    if (o.Value.guanlian == guanlian.inner_join)
                        sb.Append(" inner join ");
                    else if (o.Value.guanlian == guanlian.left_join)
                        sb.Append(" left join ");
                    else if (o.Value.guanlian == guanlian.right_join)
                        sb.Append(" right join ");
                    else if (o.Value.guanlian == guanlian.full_join)
                        sb.Append(" full join ");
                    sb.Append(o.Key);
                    sb.Append(" ");
                    sb.Append(o.Value.on);
                }
                else
                {
                    sb.Append(o.Key);
                }
            }

            //只有分页特殊一点。
            string w = pb.search(_where);

            string paixu = "";
            string strValue = requesthelper.getonevalue("cn");
            if (!string.IsNullOrEmpty(strValue) || string.IsNullOrEmpty(_orderby))
            {
                paixu = pb.orderbycolumn(_pk, pb.ordertype("desc"));
            }
            else
            {
                paixu = _orderby;
            }

            return sqlhelper.showpager(pb, sb.tostr(), _select, paixu, pb.page, pb.pagesize, w);
        }

        /// <summary>
        /// 选择的列
        /// </summary>
        /// <param name="k"></param>
        /// <returns></returns>
        public supermodel select(string k = "")
        {
            if (string.IsNullOrEmpty(k))
                _select = _pk;
            else
                _select = k;

            return this;
        }
        /// <summary>
        /// 传递where
        /// </summary>
        /// <param name="w"></param>
        /// <returns></returns>
        public supermodel where(string w)
        {
            _where = w;
            return this;
        }

        public supermodel group(string w)
        {
            _groupby = w;
            return this;
        }

        /// <summary>
        /// 排序
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        public supermodel orderby(string o)
        {
            _orderby = o;
            return this;
        }

        string _tablename
        {
            get
            {
                string tablename = (from o in tablelist where o.Value.guanlian == guanlian.empty select o).First().Key;
                return tablename.Split(' ')[0];//防止出现错误
            }
        }

        /// <summary>
        /// 加载
        /// </summary>
        /// <param name="pb"></param>
        /// <param name="key"></param>
        public void autoloaddata(pagebase pb, string key = "loadformdatas")
        {
            string sql = createselect(1);
            pb.autoloaddata(
            sqlhelper.executedataset(sql), key);
        }

        /// <summary>
        /// 一般用于删除sql语句
        /// </summary>
        public void delete()
        {
            //删除记录
            string tablename = _tablename;
            string sql = "delete from {0} where {1}";
            string w = _where;
            if (string.IsNullOrEmpty(w)) w = " 1=1 ";

            //执行删除命令
            sqlhelper.executenonquery(string.Format(sql, tablename, w));
        }

        /// <summary>
        /// 一般用于更新sql语句
        /// </summary>
        public void update()
        {
            //删除记录
            string tablename = _tablename;
            string sql = "update {0} set {1} where {2}";
            string w = _where;
            if (string.IsNullOrEmpty(w)) w = " 1=1 ";
            string set = "";

            foreach (var o in setlist)
            {
                DataRow[] drs = applicationhelper.systableinfo.Select(" tablename='" + tablename + "' and column='"+o.Key+"'");
                if(drs == null || drs.Length == 0) continue;
                string type = drs[0]["type"].tostr().ToLower();
                switch(type)
                {
                    case "int":
                    case "decimal":
                        {
                            set += "," + o.Key + "=" + o.Value;
                            break;
                        }
                    case "nvarchar":
                    case "ntext":
                    case "datetime":
                        {
                            set += "," + o.Key + "='" + o.Value.tostr().Replace("'","''") + "'";
                            break;
                        }
                }
            }
            if (set.Length > 0) set = set.Substring(1);
            else return;

            //执行更新命令
            sqlhelper.executenonquery(string.Format(sql, tablename, set, w));
        }

        /// <summary>
        /// 得到super
        /// </summary>
        /// <param name="qianzhun"></param>
        /// <returns></returns>
        public supermodel hash(string qianzhun = "")
        {
            string sql = createselect();
            Hashtable has = sqlhelper.responseonesql(sql,qianzhun);
            foreach (string s in has.Keys)
            {
                this[s.ToLower()] = has[s];
            }
            return this;
        }

        /// <summary>
        /// 返回datarows
        /// </summary>
        /// <returns></returns>
        public DataRow[] more(int top=0)
        {
            string sql = createselect(top);
            return sqlhelper.executedataset(sql).Tables[0].Select();
        }

        /// <summary>
        /// 返回一种类型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public T one<T>()
        {
            string sql = createselect(1);
            object obj = sqlhelper.executescalar(sql);
            if (obj == null) return default(T);
            Type t = typeof(T);
            if (t == typeof(int))
                return (T)Convert.ChangeType(obj.toint32(), t);
            else if (t == typeof(decimal))
                return (T)Convert.ChangeType(obj.todecimal(), t);
            else if (t == typeof(string))
                return (T)Convert.ChangeType(obj.tostr(), t);
            else if (t == typeof(DateTime))
                return (T)Convert.ChangeType(obj.todatetime(), t);

            return (T)obj;
        }

        private string createselect(int top = 0)
        {
            string tablename = _tablename;
            StringBuilder sb = new StringBuilder();
            sb.Append(" select ");
            sb.Append(" ");
            if (top > 0) sb.Append("top "+top);
            sb.Append(" ");
            sb.Append(_select);
            sb.Append(" ");
            sb.Append(" from ");
            foreach (var o in tablelist)
            {
                sb.Append(" ");
                if (o.Value.guanlian != guanlian.empty)
                {
                    if (o.Value.guanlian == guanlian.inner_join)
                        sb.Append(" inner join ");
                    else if (o.Value.guanlian == guanlian.left_join)
                        sb.Append(" left join ");
                    else if (o.Value.guanlian == guanlian.right_join)
                        sb.Append(" right join ");
                    else if (o.Value.guanlian == guanlian.full_join)
                        sb.Append(" full join ");
                    sb.Append(o.Key);
                    sb.Append(" ");
                    sb.Append(o.Value.on);
                }
                else
                {
                    sb.Append(o.Key);
                }
            }
            sb.Append(" ");
            sb.Append("where ");
            string w = _where;
            if (string.IsNullOrEmpty(w)) w = " 1=1 ";
            sb.Append(w);

            //group by 
            if (!string.IsNullOrEmpty(_groupby))
                sb.Append(" group by " + _groupby);
            
            //排序字段
            if(!string.IsNullOrEmpty(_orderby))
                sb.Append(" order by " + _orderby);

            return sb.tostr();
        }

        /// <summary>
        /// 保存，一般都是处理完毕信息后，然后进行更新。
        /// </summary>
        /// <returns></returns>
        public result save()
        {
            string tablename = _tablename;
            result result = new lxwmvc.result();

            //得到主键对应的内容
            int keyValue = this[_pk].toint32(-1);
            return _(tablename, result, keyValue);
        }

        /// <summary>
        /// 加入队列
        /// </summary>
        /// <param name="key"></param>
        /// <param name="v"></param>
        /// <returns></returns>
        public supermodel add(string key, object v)
        {
            this[key.ToLower().Trim()] = v;
            return this;
        }
      
        public result saveorupdate(Action<supermodel> insert = null, Action<supermodel> update = null)
        {
            string tablename = _tablename;
            result result = new lxwmvc.result();            
            
            //得到主键对应的内容
            int keyValue = this[_pk].toint32(-1);

            //给一些未赋值的变量赋值
            if (keyValue > 0)
            {
                //如果是更新,主要处理checkbox
                string keys = requesthelper.getonevalue("_lxw_column_checkbox_");
                if (!string.IsNullOrEmpty(keys))
                {
                    foreach (string s in keys.Split(','))
                    {
                        if (s.Length == 0) continue;
                        if (this.Contains(s))  //这里是写死了，如果允许，就是1，不允许就是0，只有两种状态，是否允许再数据库中配置一下？
                            this[s] = this[s].tostr() == "on" ? 1 : 0;
                        else
                            this[s] = 0;
                    }
                }

                if (update != null) update(this);
            }
            else
            {
                //动态赋值
                string keys = requesthelper.getonevalue("_lxw_column_keys_");
                string defaultvalue = requesthelper.getonevalue("_lxw_column_default_value_");
                string checkboxs = requesthelper.getonevalue("_lxw_column_checkbox_");
                string datetimes = requesthelper.getonevalue("_lxw_column_datetime_");

                if (keys.Length > 0)
                {
                    foreach (string s0 in keys.Split(','))
                    {
                        //去掉前面的_
                        if (string.IsNullOrEmpty(s0)) continue;
                        string s = s0.TrimStart(new char[] { '_' });

                        //把不符合要求的ck去掉，比如一次性传递了10个checkbox？？
                        //todo:这里是存在问题的。
                        if (stringhelper.findvalue(checkboxs, s0))
                        {
                            this[s] = this[s].tostr() == "on" ? 1 : 0;
                            continue;
                        }

                        if (stringhelper.findvalue(datetimes, s0))
                        {
                            this[s] = DateTime.Now;
                            continue;
                        }
                        //赋值
                        this[s] = requesthelper.fromurlparamsgetvaluebykey(defaultvalue, s0);
                    }
                }

                if (insert != null) insert(this);
            }

            return _(tablename, result, keyValue);
        }
        private lxwmvc.result _(string tablename, result result, int keyValue)
        {
            Hashtable has = new Hashtable();
            foreach (string s in this.Keys)
            {
                has[s.ToLower()] = this[s];
            }

            result.has = has;
            if (this.ContainsKey(_pk) && has[_pk] != null && has[_pk].ToString().Length > 0)
            {
                result.action = action.update;
            }
            else
            {
                //这里是最容易出现错误的，当数据量越来越庞大的时候。
                result.action = action.insert;
            }

            string updateColumns = "";
            string insertColumns = "";
            string insertValues = "";

            DataRow[] drs = applicationhelper.systableinfo.Select(" tablename='" + tablename + "'");
            foreach (DataRow d in drs)
            {
                string s = d["column"].tostr().ToLower();
                string _null = d["null"].tostr().ToLower();
                string _type = d["type"].tostr().ToLower();
                //主键不要更新
                if (s.Equals(_pk, StringComparison.OrdinalIgnoreCase)) continue;
                //这里比较特殊 20120610
                if (s.IndexOf('=') != -1) continue;
                //允许为空，并且集合中存在
                if (_null == "1" && !has.ContainsKey(s)) continue;

                //20121023 lxw
                //这句话非常好，只更新传递的值，其他值不更新
                if (result.action == action.update && !has.ContainsKey(s)) continue;

                if (_type == "int")
                {
                    updateColumns += "," + s + "=";
                    insertColumns += "," + s + "";
                    insertValues += "," + has[s].toint32(0) + "";
                    updateColumns += "" + has[s].toint32(0) + "";
                }
                else if (_type == "decimal")
                {
                    updateColumns += "," + s + "=";
                    insertColumns += "," + s + "";
                    insertValues += "," + has[s].todecimal(0) + "";
                    updateColumns += "" + has[s].todecimal(0) + "";
                }
                else if (_type == "datetime")
                {
                    try
                    {
                        DateTime time = has[s].todatetime("1900-1-1", true);
                        updateColumns += "," + s + "=";
                        insertColumns += "," + s + "";
                        insertValues += ",'" + time + "'";
                        updateColumns += "'" + time + "'";
                    }
                    catch { }
                }
                else
                {
                    updateColumns += "," + s + "=";
                    insertColumns += "," + s + "";
                    insertValues += ",'" + has[s].tostr().Replace("'", "''") + "'";
                    updateColumns += "'" + has[s].tostr().Replace("'", "''") + "'";
                }
            }
            string updateSql = "update {0} set {1} where {2}={3}";
            string InsertSql = "insert into {0}({1}) values({2});select @@identity";


            if (result.action == action.update)
            {
                //这里修改过错误
                sqlhelper.executescalar(string.Format(updateSql, tablename, updateColumns.TrimStart(','), _pk, keyValue));
                result.identity = keyValue;
            }
            else
            {
                result.identity = sqlhelper.executescalar(string.Format(InsertSql, tablename, insertColumns.TrimStart(','), insertValues.TrimStart(','))).toint32();
            }

            return result;
        }

        public supermodel inner(string tablename,string on)
        {
            tablename = tablename.ToLower();
            tablelist[tablename] = new ti() { guanlian = guanlian.inner_join, on = on };
            return this;
        }
        public supermodel left(string tablename, string on)
        {
            tablename = tablename.ToLower();
            tablelist[tablename] = new ti() { guanlian = guanlian.left_join, on = on };
            return this;
        }
        public supermodel right(string tablename, string on)
        {
            tablename = tablename.ToLower();
            tablelist[tablename] = new ti() { guanlian = guanlian.right_join, on = on };
            return this;
        }
        public supermodel full(string tablename, string on)
        {
            tablename = tablename.ToLower();
            tablelist[tablename] = new ti() { guanlian = guanlian.full_join, on = on };
            return this;
        }
        
        /// <summary>
        /// 不能外部实例化
        /// </summary>
        private supermodel()
        {

        }



        private string _groupby = "";
    }
}